/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package ProgramSeat;

import Course.CourseBean;
import java.sql.*;
import java.util.Vector;

/**
 *
 * @author mishra
 */
public class Query {

    static Connection con = null;

    public static int add(ProgramSeatBean pb) throws SQLException {
        int i = 0, k = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("insert into program_seat values(programseatid.nextval,?,?,?,?,sysdate)");
            pre.setInt(1, pb.getCourseId());
            pre.setString(2, pb.getStream());
            pre.setInt(3, pb.getTotalSeat());
            pre.setString(4, pb.getProgramSeatUnique());
            i = pre.executeUpdate();
            if (i != 0) {
                k = getMaxId();
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return k;
    }

    public static int getMaxId() throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select max(program_seat_id) from program_seat");
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                i = rs.getInt(1);
            }
        } catch (Exception e) {
            System.out.println(e);
        }
        return i;
    }

    public static ProgramSeatBean viewSpecific(String cname, String val) throws SQLException {
        ProgramSeatBean pb = new ProgramSeatBean();
        CourseBean cb = new CourseBean();
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from program_seat left join course on program_seat.course_id=course.course_id where " + cname + "=?");
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                pb.setProgramSeatId(rs.getInt(1));
                pb.setCourseId(rs.getInt(2));
                pb.setStream(rs.getString(3));
                pb.setTotalSeat(rs.getInt(4));
                pb.setProgramSeatUnique(rs.getString(5));
                pb.setEntryDate(rs.getDate(6));
                cb.setCourseId(rs.getInt(7));
                cb.setCourseName(rs.getString(8));
                cb.setCourseType(rs.getInt(9));
                cb.setCoursePeriod(rs.getInt(10));
                cb.setStream(rs.getString(11));
                cb.setEntryDate(rs.getDate(12));
                pb.setCourseBean(cb);

            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return pb;
    }

    public static Vector viewAll() throws SQLException {
        Vector<Object> list = new Vector<Object>();

        try {

            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from program_seat left join course on program_seat.course_id=course.course_id");
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                ProgramSeatBean pb = new ProgramSeatBean();
                CourseBean cb = new CourseBean();
                pb.setProgramSeatId(rs.getInt(1));
                pb.setCourseId(rs.getInt(2));
                pb.setStream(rs.getString(3));
                pb.setTotalSeat(rs.getInt(4));
                pb.setProgramSeatUnique(rs.getString(5));
                pb.setEntryDate(rs.getDate(6));
                cb.setCourseId(rs.getInt(7));
                cb.setCourseName(rs.getString(8));
                cb.setCourseType(rs.getInt(9));
                cb.setCoursePeriod(rs.getInt(10));
                cb.setStream(rs.getString(11));
                cb.setEntryDate(rs.getDate(12));
                pb.setCourseBean(cb);
                list.add(pb);

            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return list;
    }
    public static int edit(ProgramSeatBean pb) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("update program_seat set course_id=?, stream=?, total_seat=?, program_seat_unique=?, entrydate=sysdate where program_seat_id=?");
            pre.setInt(1, pb.getCourseId());
            pre.setString(2, pb.getStream());
            pre.setInt(3, pb.getTotalSeat());
            pre.setString(4, pb.getCourseId()+"/"+pb.getProgramSeatId());
            pre.setInt(5, pb.getProgramSeatId());
            i = pre.executeUpdate();

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return i;
    }
    public static int delete(int id) throws SQLException
        {
            int i=0;
            try
            {
              con=DBConnection.Connection.getConnection();
              PreparedStatement pre=con.prepareStatement("delete from program_seat where program_seat_id=?");
              pre.setInt(1,id);
              i=pre.executeUpdate();
            }
            catch(Exception e)
            {
               return i; 
            }
            finally
            {
              con.close();  
            }
            return i;
        }
    
    public static Vector viewProgramSeat(int courseid) throws SQLException {
        Vector<Object> list = new Vector<Object>();

        try {

            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from program_seat where course_id=?");
            pre.setInt(1, courseid);
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                ProgramSeatBean pb = new ProgramSeatBean();
                CourseBean cb = new CourseBean();
                pb.setProgramSeatId(rs.getInt(1));
                pb.setCourseId(rs.getInt(2));
                pb.setStream(rs.getString(3));
                pb.setTotalSeat(rs.getInt(4));
                pb.setProgramSeatUnique(rs.getString(5));
                pb.setEntryDate(rs.getDate(6));
                cb.setCourseId(rs.getInt(7));
                cb.setCourseName(rs.getString(8));
                cb.setCourseType(rs.getInt(9));
                cb.setCoursePeriod(rs.getInt(10));
                cb.setStream(rs.getString(11));
                cb.setEntryDate(rs.getDate(12));
                pb.setCourseBean(cb);
                list.add(pb);

            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return list;
    }
}
